package com.huayue.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.Product;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 商品
 */
@Component
public class ProductDao implements BaseDao<Product> {
    @Override
    public int insert(Product entity) {
        return DBHelper.executeUpdate(
            "INSERT INTO product (product_number, supplier_id, product_type_id, name, price, inprice, unit, spec, remark) VALUES (?,?,?,?,?,?,?,?,?);",
            entity.getProduct_number(), entity.getSupplier_id(), entity.getProduct_type_id(), entity.getName(),
            entity.getPrice(), entity.getInprice(), entity.getUnit(), entity.getSpec(), entity.getRemark());
    }

    @Override
    public int update(Product entity) {
        return DBHelper.executeUpdate(
            "UPDATE product SET product_number=?, supplier_id=?, product_type_id=?, name=?, price=?, inprice=?, unit=?, spec=?, remark=? WHERE id=?;",
            entity.getProduct_number(), entity.getSupplier_id(), entity.getProduct_type_id(), entity.getName(),
            entity.getPrice(), entity.getInprice(), entity.getUnit(), entity.getSpec(), entity.getRemark(),
            entity.getId());
    }

    @Override
    public int delete(Product entity) {
        // 将库存表状态改为禁用
        DBHelper.executeUpdate("update depot set is_status = 0 where product_id=?", entity.getId());
        return DBHelper.executeUpdate("update product set is_deleted=1 where id=?", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery("select * from product where is_deleted=0");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        Product product = (Product)obj[0];
        String sql = "select p.*,pt.name ptname,su.name suname from product p "
            + "left join product_type pt on p.product_type_id=pt.id "
            + "left join supplier su on p.supplier_id=su.id where p.is_deleted=0 ";
        if (product.getName() != null && !"".equals(product.getName())) {
            sql += " and p.name like '%" + product.getName() + "%' ";
        }
        if (product.getProduct_type_id() != null && product.getProduct_type_id() != 0) {
            sql += " and pt.id = " + product.getProduct_type_id();
        }
        if (product.getSupplier_id() != null && product.getSupplier_id() != 0) {
            sql += " and su.id=" + product.getSupplier_id();
        }
        return DBHelper.executeQuery(sql + " order by id desc limit ?,?", page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(Product entity) {
        return DBHelper.executeQuery("select * from product where is_deleted=0 and id=?", entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        Product product = (Product)obj[0];
        String sql = "select count(*) counts from product p " + "left join product_type pt on p.product_type_id=pt.id "
            + "left join supplier su on p.supplier_id=su.id where p.is_deleted=0 ";
        if (product.getName() != null && !"".equals(product.getName())) {
            sql += " and p.name like '%" + product.getName() + "%' ";
        }
        if (product.getProduct_type_id() != null && product.getProduct_type_id() != 0) {
            sql += " and pt.id = " + product.getProduct_type_id();
        }
        if (product.getSupplier_id() != null && product.getSupplier_id() != 0) {
            sql += " and su.id=" + product.getSupplier_id();
        }
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

    /**
     * 查询当前库存量小于标准库存量的产品
     * 
     * @return
     */
    public List<Map<String, Object>> findProductByReal_Number() {
        return DBHelper.executeQuery(
            "select pd.id,pd.name,pd.product_number,pd.inprice,pd.supplier_id,pd.unit from product pd left join depot d on d.product_id=pd.id "
                + " where pd.is_deleted=0 and d.is_deleted=0 and d.real_number<d.normal_number ORDER BY d.real_number asc");
    }

    public List<Map<String, Object>> findAllProduct() {
        String sql =
            "select pd.id,pd.name,pd.product_number,pd.inprice,pd.supplier_id,pd.unit from product pd left join depot d on d.product_id=pd.id "
                + " where pd.is_deleted=0 and d.is_deleted=0 and d.is_status=1 ORDER BY d.real_number asc";
        return DBHelper.executeQuery(sql);
    }
}
